/*stuinfo.c*/
#include "mysql.h"
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

struct student // 对应于表中的字段
{
    int id;  // 学号
    char name[20]; // 姓名
    char sex[6]; // 性别
    char mailbox[40];  // 邮箱地址
    char studep[100]; // 所在院系
};

/* 初始化数据库 */
MYSQL * initial(MYSQL *con){
    if (mysql_library_init(0, NULL, NULL)) {
        fprintf(stderr, "Could not initialize MySQL library.\n");
        return NULL; // 修改此处，返回 NULL 指针
    } 
    con = mysql_init(NULL);
    return con;
}

/* 连接数据库 */
MYSQL * myconnect(MYSQL *con,char *username,char *passwd,char *hostip,char *db){
    if( mysql_real_connect(con,"localhost","root","root",db, 0, NULL, 0)==NULL ) {
        printf("connect failed\n");
    }
    return con;
}

void createDB(MYSQL *con,char *cmd) {
    if (mysql_query(con,cmd)) 
    {
        printf("createDB failed");
    }
    else
        printf("Create Database successful\n");
}

void createTable(MYSQL *con,char *db,char *cmd){
    if (  mysql_select_db(con,db) ){
        printf("select DB failed\n");
    }    
    if (mysql_query(con,cmd)) 
    {
        printf("createTAble failed\n");
    }
    else
        printf("Create table successful\n");
}

void displayTable(MYSQL *con,char *query)
{
    MYSQL_RES *mysql_res;
    MYSQL_ROW mysql_row;  
    int i,j;
    int num_row,num_col;
    if(  ( mysql_real_query(con, query, (unsigned int)strlen(query))) != 0){
        printf("Query failed\n");
    }
    // 存储查询结果
    if ( (mysql_res = mysql_store_result(con) ) == NULL ){
        printf("Store result failed\n");
    }
    // 获取查询结果的行数和列数
    num_row = mysql_num_rows(mysql_res);
    num_col = mysql_num_fields(mysql_res);
    printf("row number = %1u, col number = %1u\n", num_row, num_col);
    printf("************************************************\n");
    // 逐行显示结果
    for(i=0; i<num_row; i++){
        // 从结果集中获取一行数据
        if ((mysql_row = mysql_fetch_row(mysql_res)) == NULL){
            break;
        }
        // 获取每列的长度
        mysql_fetch_lengths(mysql_res);
        for(j=0; j<num_col; j++){
            // 打印一行中各列的值
            printf("%s\t", mysql_row[j]?mysql_row[j]:"NULL");
        }
        printf("\n");
    }
    printf("************************************************\n");
    // 释放结果集的查询结果
    mysql_free_result(mysql_res);
}

void insertData(MYSQL *con){
    struct student student1;
    char query[1024];
    printf("please input student id : \n");
    scanf("%d", &student1.id);
    getchar();
    printf("please input student name:\n ");
    scanf("%s", student1.name);
    getchar();
    printf("please input student sex(male/female):\n ");
    scanf("%s", student1.sex);
    getchar();
    printf("please input student phone: \n");
    scanf("%s", student1.mailbox);
    getchar();
    printf("please input student department: \n");
    scanf("%s", student1.studep);
    getchar();
    sprintf(query, "insert into stuinfo(id, stuname, gender,mailbox,studep) values(%d, '%s', '%s', '%s', '%s')", student1.id, student1.name, student1.sex, student1.mailbox, student1.studep);
    if( ( mysql_real_query(con, query, (unsigned int)strlen(query))) != 0 ){
        mysql_rollback(con);
        printf("insert failed\n");
    }
    mysql_commit(con);
    displayTable(con,"select * from stuinfo");
}

void deleteData(MYSQL *con){
    int id;
    char query[1024];
    printf("please input student id to be deleted\n");
    scanf("%d",&id);
    sprintf(query, "delete from stuinfo where id=%d",id);  
    if( ( mysql_real_query(con, query, (unsigned int)strlen(query))) != 0 ){
        mysql_rollback(con);
        printf("delete failed\n");
    }
    mysql_commit(con);
    if ( mysql_affected_rows(con)>0 )
        printf("delete successful\n");
    displayTable(con,"select * from stuinfo");
}

int menu(){
    int choose;
    {
        printf("*********************************\n");
        printf("******student information system****\n");
        printf("*********************************\n");
        printf("1.  create database\n");     
        printf("2.  create table\n");
        printf("3.  insert data\n");
        printf("4.  delete data\n");
        printf("5.  update data\n");
        printf("please input choose\n");
        scanf("%d",&choose);
        getchar();
        return choose;
    }
}

void handleMenu(MYSQL *mysql){
    int choose;
    while (1) {
        choose=menu();
        switch(choose) {     
            case 1:   createDB(mysql,"create database student");break;
            case 2:   createTable(mysql,"student","create table stuinfo(id INT, stuname TEXT, gender TEXT,mailbox TEXT,studep TEXT)");break;
            case 3:    insertData(mysql); break;   
            case 4:    deleteData(mysql); break;
            case 5:    //updateData(mysql); break;
            case 0:    printf("exit\n"); return; break;
        }      
    }   
}


int main(void)
{
    MYSQL *mysql_handle=NULL;
    mysql_handle=initial(mysql_handle);
    if ( NULL==mysql_handle ) {
        fprintf(stderr, "%s\n","initialize failed");
        exit(1);
    }      
    mysql_handle=myconnect(mysql_handle,"root","root","localhost","");	
    handleMenu(mysql_handle);
    mysql_close(mysql_handle);
    mysql_library_end();
    return(0);
}